/***********************************************************************************************************************************
 * Name:	Job_PurgeHistory.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com/frank@sqlnerd.me)
 * Summary:	
 */
SET IMPLICIT_TRANSACTIONS OFF; WHILE 0 < @@TRANCOUNT ROLLBACK;
USE [msdb];
GO

IF NOT EXISTS (SELECT * FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance' AND category_class = 1)
  EXECUTE msdb.dbo.sp_add_category @class= N'JOB', @type= N'LOCAL', @name= N'Database Maintenance';
GO

-- !DBA — Purge History ------------------------------------------------------------------------------------------------------------
DECLARE
	@JobName			SYSNAME			= N'!DBA — Purge History',
	@LiteSpeed			BIT				= ISNULL((SELECT 1 FROM master.dbo.sysobjects WHERE xtype = 'X' AND name = 'xp_sqllitespeed_version'),0),
	@OutputFileName		NVARCHAR(255)	= master.DBAdmin.GetInstancePath(N'WorkingDirectory') + N'Purge_History.txt',
	@jobID				BINARY(16);

SELECT @jobID= job_id FROM msdb.dbo.sysjobs WHERE name = @JobName;
IF @jobID IS Null BEGIN
	EXECUTE msdb.dbo.sp_add_job
		@job_name			= @JobName,
		@category_name		= N'Database Maintenance',
		@owner_login_name	= N'sa',
		@job_id				= @jobID OUTPUT;
	EXECUTE msdb.dbo.sp_add_jobserver @job_id= @jobID, @server_name= N'(LOCAL)';
END ELSE EXECUTE msdb.dbo.sp_delete_jobstep @job_id= @jobID, @step_id= 0;

EXECUTE msdb.dbo.sp_update_job
	@job_id						= @jobID,
	@notify_level_page			= 0,	-- Never
	@notify_level_netsend		= 0,	-- Never
	@notify_level_eventlog		= 0,	-- Never
	@notify_level_email			= 2,	-- On Failure
	@notify_email_operator_name	= N'(DB)A-Team';

EXECUTE msdb.dbo.sp_add_jobstep
	@job_id				= @jobID,
	@step_name			= N'Purge History',
	@step_id			= 1,
	@on_success_action	= 1,
	@on_success_step_id	= 0,
	@on_fail_action		= 2,
	@on_fail_step_id	= 0,
	@retry_attempts		= 2, 
	@retry_interval		= 1,
	@command			= N'EXECUTE DBAdmin.PurgeHistory;',
	@output_file_name	= @OutputFileName;

BEGIN TRY;
  EXECUTE msdb.dbo.sp_attach_schedule @job_name= N'!DBA — Purge History', @schedule_name= N'PurgeHistory';
END TRY BEGIN CATCH END CATCH;
GO